﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Common;
using System.Data.SqlClient;
using System.Configuration;
using System.Data.Odbc;
using System.Data.OleDb;
using System.Data;
using System.IO;

namespace BIExtAuth.DBUtility
{
    /// <summary>
    /// ClassName：数据库操作辅助类
    /// Author：scc-John
    /// CreateDate:2013-09-19
    /// </summary>
    public class DBHelper : IDisposable
    {
        #region 私有属性
        /// <summary>
        /// 数据库连接字符串
        /// </summary>
        private string strConnectionString;
        /// <summary>
        /// 数据库连接对象
        /// </summary>
        private DbConnection objConnection;
        /// <summary>
        /// 数据库命令对象
        /// </summary>
        private DbCommand objCommand;
        /// <summary>
        /// 数据库提供程序实例
        /// </summary>
        private DbProviderFactory objFactory = null;
        /// <summary>
        /// 是否记录句柄错误
        /// </summary>
        private bool boolHandleErrors;
        /// <summary>
        /// 最后一个错误
        /// </summary>
        private string strLastError;
        /// <summary>
        /// 日志记录是否成功
        /// </summary>
        private bool boolLogError;
        /// <summary>
        /// 日志记录文件
        /// </summary>
        private string strLogFile;

        #endregion

        #region 实例化DBHelper
        /// <summary>
        /// 实例化DBHelper
        /// </summary>
        /// <param name="connectionstring">连接字符串</param>
        /// <param name="provider">数据库提供程序</param>
        public DBHelper(string connectionstring, Providers provider)
        {
            strConnectionString = connectionstring;
            switch (provider)
            {
                case Providers.SqlServer:
                    objFactory = SqlClientFactory.Instance;
                    break;
                case Providers.OleDb:
                    objFactory = OleDbFactory.Instance;
                    break;
                case Providers.ODBC:
                    objFactory = OdbcFactory.Instance;
                    break;
                case Providers.ConfigDefined:
                    string providername = ConfigurationManager.ConnectionStrings["connectionstring"].ProviderName;
                    switch (providername)
                    {
                        case "System.Data.SqlClient":
                            objFactory = SqlClientFactory.Instance;
                            break;
                        case "System.Data.OleDb":
                            objFactory = OleDbFactory.Instance;
                            break;
                        case "System.Data.Odbc":
                            objFactory = OdbcFactory.Instance;
                            break;
                    }
                    break;

            }
            objConnection = objFactory.CreateConnection();
            objCommand = objFactory.CreateCommand();

            objConnection.ConnectionString = strConnectionString;
            objCommand.Connection = objConnection;
        }

        /// <summary>
        /// 实例化DBHelper(从config文件读取连接字符串)
        /// </summary>
        /// <param name="provider">数据库提供程序</param>
        public DBHelper(Providers provider)
            : this(ConfigurationManager.ConnectionStrings["connectionstring"].ConnectionString, provider)
        {
        }

        /// <summary>
        /// 实例化DBHelper(自定义连接字符串)
        /// </summary>
        /// <param name="connectionstring">数据库连接字符串</param>
        public DBHelper(string connectionstring)
            : this(connectionstring, Providers.SqlServer)
        {
        }

        /// <summary>
        /// 实例化DBHelper(从config文件读取连接字符串和数据库提供程序)
        /// </summary>
        public DBHelper()
            : this(ConfigurationManager.ConnectionStrings["connectionstring"].ConnectionString, Providers.ConfigDefined)
        {
        }
        #endregion

        #region 属性设置
        /// <summary>
        /// 句柄错误
        /// </summary>
        public bool HandleErrors
        {
            get
            {
                return boolHandleErrors;
            }
            set
            {
                boolHandleErrors = value;
            }
        }

        /// <summary>
        /// 最后一个错误
        /// </summary>
        public string LastError
        {
            get
            {
                return strLastError;
            }
        }

        /// <summary>
        /// 日志错误
        /// </summary>
        public bool LogErrors
        {
            get
            {
                return boolLogError;
            }
            set
            {
                boolLogError = value;
            }
        }

        /// <summary>
        /// 日志文件
        /// </summary>
        public string LogFile
        {
            get
            {
                return strLogFile;
            }
            set
            {
                strLogFile = value;
            }
        }

        /// <summary>
        /// 命令对象
        /// </summary>
        public DbCommand Command
        {
            get
            {
                return objCommand;
            }
        }
        #endregion

        #region 设置语句参数
        /// <summary>
        /// 设置参数
        /// </summary>
        /// <param name="name">参数名称</param>
        /// <param name="value">参数值</param>
        /// <returns>集合中 System.Data.Common.DbParameter 对象的索引</returns>
        public int AddParameter(string name, object value)
        {
            DbParameter p = objFactory.CreateParameter();
            p.ParameterName = name;
            p.Value = value;
            return objCommand.Parameters.Add(p);
        }

        /// <summary>
        /// 设置参数
        /// </summary>
        /// <param name="parameter">参数对象</param>
        /// <returns>集合中 System.Data.Common.DbParameter 对象的索引</returns>
        public int AddParameter(DbParameter parameter)
        {
            return objCommand.Parameters.Add(parameter);
        }
        #endregion

        #region 数据库常用执行SQL操作方法
        /// <summary>
        /// 执行SQL命令，返回受影响行数
        /// </summary>
        /// <param name="query">SQL命令</param>
        /// <returns>受影响的行数</returns>
        public int ExecuteNonQuery(string query)
        {
            return ExecuteNonQuery(query, CommandType.Text, ConnectionState.CloseOnExit);
        }

        /// <summary>
        /// 执行SQL命令，返回受影响行数
        /// </summary>
        /// <param name="query">SQL命令</param>
        /// <param name="commandtype">SQL命令类型</param>
        /// <returns>受影响的行数</returns>
        public int ExecuteNonQuery(string query, CommandType commandtype)
        {
            return ExecuteNonQuery(query, commandtype, ConnectionState.CloseOnExit);
        }

        /// <summary>
        /// 执行SQL命令，返回受影响行数
        /// </summary>
        /// <param name="query">SQL命令</param>
        /// <param name="connectionstate">连接状态</param>
        /// <returns>受影响行数</returns>
        public int ExecuteNonQuery(string query, ConnectionState connectionstate)
        {
            return ExecuteNonQuery(query, CommandType.Text, connectionstate);
        }

        /// <summary>
        /// 执行SQL命令，返回受影响行数
        /// </summary>
        /// <param name="query">SQL命令</param>
        /// <param name="commandtype">SQL命令类型</param>
        /// <param name="connectionstate">连接状态</param>
        /// <returns>受影响行数</returns>
        public int ExecuteNonQuery(string query, CommandType commandtype, ConnectionState connectionstate)
        {
            objCommand.CommandText = query;
            objCommand.CommandType = commandtype;
            int i = -1;
            try
            {
                if (objConnection.State == System.Data.ConnectionState.Closed)
                {
                    objConnection.Open();
                }
                i = objCommand.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                HandleExceptions(ex);
            }
            finally
            {
                objCommand.Parameters.Clear();
                if (connectionstate == ConnectionState.CloseOnExit)
                {
                    objConnection.Close();
                }
            }

            return i;
        }

        /// <summary>
        /// 执行查询，并返回查询所返回的结果集中第一行的第一列。所有其他的列和行将被忽略
        /// </summary>
        /// <param name="query"></param>
        /// <returns>结果集中第一行的第一列</returns>
        public object ExecuteScalar(string query)
        {
            return ExecuteScalar(query, CommandType.Text, ConnectionState.CloseOnExit);
        }

        /// <summary>
        /// 执行查询，并返回查询所返回的结果集中第一行的第一列。所有其他的列和行将被忽略
        /// </summary>
        /// <param name="query">SQL命令</param>
        /// <param name="commandtype">SQL命令类型</param>
        /// <returns>结果集中第一行的第一列</returns>
        public object ExecuteScalar(string query, CommandType commandtype)
        {
            return ExecuteScalar(query, commandtype, ConnectionState.CloseOnExit);
        }

        /// <summary>
        /// 执行查询，并返回查询所返回的结果集中第一行的第一列。所有其他的列和行将被忽略
        /// </summary>
        /// <param name="query">SQL命令</param>
        /// <param name="connectionstate">连接状态</param>
        /// <returns>结果集中第一行的第一列</returns>
        public object ExecuteScalar(string query, ConnectionState connectionstate)
        {
            return ExecuteScalar(query, CommandType.Text, connectionstate);
        }

        /// <summary>
        /// 执行查询，并返回查询所返回的结果集中第一行的第一列。所有其他的列和行将被忽略
        /// </summary>
        /// <param name="query">SQL命令</param>
        /// <param name="commandtype">SQL命令类型</param>
        /// <param name="connectionstate">连接状态</param>
        /// <returns>结果集中第一行的第一列</returns>
        public object ExecuteScalar(string query, CommandType commandtype, ConnectionState connectionstate)
        {
            objCommand.CommandText = query;
            objCommand.CommandType = commandtype;
            object o = null;
            try
            {
                if (objConnection.State == System.Data.ConnectionState.Closed)
                {
                    objConnection.Open();
                }
                o = objCommand.ExecuteScalar();
            }
            catch (Exception ex)
            {
                HandleExceptions(ex);
            }
            finally
            {
                objCommand.Parameters.Clear();
                if (connectionstate == ConnectionState.CloseOnExit)
                {
                    objConnection.Close();
                }
            }

            return o;
        }

        /// <summary>
        /// 执行查询，返回 System.Data.Common.DbDataReader
        /// </summary>
        /// <param name="query">SQL命令</param>
        /// <returns>返回 System.Data.Common.DbDataReader</returns>
        public DbDataReader ExecuteReader(string query)
        {
            return ExecuteReader(query, CommandType.Text, ConnectionState.CloseOnExit);
        }

        /// <summary>
        /// 执行查询，返回 System.Data.Common.DbDataReader
        /// </summary>
        /// <param name="query">SQL命令</param>
        /// <param name="commandtype">SQL命令类型</param>
        /// <returns>返回 System.Data.Common.DbDataReader</returns>
        public DbDataReader ExecuteReader(string query, CommandType commandtype)
        {
            return ExecuteReader(query, commandtype, ConnectionState.CloseOnExit);
        }

        /// <summary>
        /// 执行查询，返回 System.Data.Common.DbDataReader
        /// </summary>
        /// <param name="query">SQL命令</param>
        /// <param name="connectionstate">连接状态</param>
        /// <returns>返回 System.Data.Common.DbDataReader</returns>
        public DbDataReader ExecuteReader(string query, ConnectionState connectionstate)
        {
            return ExecuteReader(query, CommandType.Text, connectionstate);
        }

        /// <summary>
        /// 执行查询，返回 System.Data.Common.DbDataReader
        /// </summary>
        /// <param name="query">SQL命令</param>
        /// <param name="commandtype">SQL命令类型</param>
        /// <param name="connectionstate"></param>
        /// <returns>返回 System.Data.Common.DbDataReader</returns>
        public DbDataReader ExecuteReader(string query, CommandType commandtype, ConnectionState connectionstate)
        {
            objCommand.CommandText = query;
            objCommand.CommandType = commandtype;
            DbDataReader reader = null;
            try
            {
                if (objConnection.State == System.Data.ConnectionState.Closed)
                {
                    objConnection.Open();
                }
                if (connectionstate == ConnectionState.CloseOnExit)
                {
                    reader = objCommand.ExecuteReader(CommandBehavior.CloseConnection);
                }
                else
                {
                    reader = objCommand.ExecuteReader();
                }

            }
            catch (Exception ex)
            {
                HandleExceptions(ex);
            }
            finally
            {
                objCommand.Parameters.Clear();
            }

            return reader;
        }

        /// <summary>
        /// 执行查询，返回DataSet
        /// </summary>
        /// <param name="query">SQL命令</param>
        /// <returns>返回DataSet</returns>
        public DataSet ExecuteDataSet(string query)
        {
            return ExecuteDataSet(query, CommandType.Text, ConnectionState.CloseOnExit);
        }

        /// <summary>
        /// 执行查询，返回DataSet
        /// </summary>
        /// <param name="query">SQL命令</param>
        /// <param name="commandtype">SQL命令类型</param>
        /// <returns>返回DataSet</returns>
        public DataSet ExecuteDataSet(string query, CommandType commandtype)
        {
            return ExecuteDataSet(query, commandtype, ConnectionState.CloseOnExit);
        }

        /// <summary>
        /// 执行查询，返回DataSet
        /// </summary>
        /// <param name="query">SQL命令</param>
        /// <param name="connectionstate">SQL命令类型</param>
        /// <returns>返回DataSet</returns>
        public DataSet ExecuteDataSet(string query, ConnectionState connectionstate)
        {
            return ExecuteDataSet(query, CommandType.Text, connectionstate);
        }

        /// <summary>
        /// 执行查询，返回DataSet
        /// </summary>
        /// <param name="query">SQL命令</param>
        /// <param name="commandtype">SQL命令类型</param>
        /// <param name="connectionstate">连接状态</param>
        /// <returns>返回DataSet</returns>
        public DataSet ExecuteDataSet(string query, CommandType commandtype, ConnectionState connectionstate)
        {
            //返回实现 System.Data.Common.DbDataAdapter 类的提供程序的类的一个新实例
            DbDataAdapter adapter = objFactory.CreateDataAdapter();
            objCommand.CommandText = query;
            objCommand.CommandType = commandtype;
            adapter.SelectCommand = objCommand;
            DataSet ds = new DataSet();
            try
            {
                adapter.Fill(ds);
            }
            catch (Exception ex)
            {
                HandleExceptions(ex);
            }
            finally
            {
                objCommand.Parameters.Clear();
                if (connectionstate == ConnectionState.CloseOnExit)
                {
                    if (objConnection.State == System.Data.ConnectionState.Open)
                    {
                        objConnection.Close();
                    }
                }
            }
            return ds;
        }
        #endregion

        #region 事务处理方法
        /// <summary>
        /// 开始事务
        /// </summary>
        public void BeginTransaction()
        {
            if (objConnection.State == System.Data.ConnectionState.Closed)
            {
                objConnection.Open();
            }
            objCommand.Transaction = objConnection.BeginTransaction();
        }

        /// <summary>
        /// 提交事务
        /// </summary>
        public void CommitTransaction()
        {
            objCommand.Transaction.Commit();
            objConnection.Close();
        }

        /// <summary>
        /// 回滚事务
        /// </summary>
        public void RollbackTransaction()
        {
            objCommand.Transaction.Rollback();
            objConnection.Close();
        }
        #endregion

        #region 异常处理和日志记录
        /// <summary>
        /// 句柄异常处理
        /// </summary>
        /// <param name="ex">异常</param>
        private void HandleExceptions(Exception ex)
        {
            if (LogErrors)
            {
                WriteToLog(ex.Message);
            }
            if (HandleErrors)
            {
                strLastError = ex.Message;
            }
            else
            {
                throw ex;
            }
        }

        /// <summary>
        /// 记录日志
        /// </summary>
        /// <param name="msg">日志内容</param>
        private void WriteToLog(string msg)
        {
            StreamWriter writer = File.AppendText(LogFile);
            writer.WriteLine(DateTime.Now.ToString() + " - " + msg);
            writer.Close();
        }
        #endregion

        #region 资源释放
        /// <summary>
        /// 释放资源
        /// </summary>
        public void Dispose()
        {
            if (objConnection != null)
            {
                objConnection.Close();
                objConnection.Dispose();
            }
            if (objCommand != null)
            {
                objCommand.Dispose();
            }
        }
        #endregion
    }

    #region SQL命令执行后连接状态的动作标记
    /// <summary>
    /// 连接状态
    /// </summary>
    public enum ConnectionState
    {
        /// <summary>
        /// 执行查询后保持Open状态
        /// </summary>
        KeepOpen,
        /// <summary>
        /// 执行查询后关闭连接
        /// </summary>
        CloseOnExit
    }
    #endregion
}
